package com.java.dao.impl;

import com.java.bean.User;
import com.java.dao.BaseUserDao;
import com.java.exception.DuplicateCodeException;
import com.java.service.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoMysql implements BaseUserDao {
    public static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) size," +
            "COUNT(TO_DAYS(REGISTERTIME)=TO_DAYS(NOW()) OR NULL) day" +
            " FROM USER";
    //用于查询数据库中的所有用户信息
    public static final String SQL_FIND_ALL = "SELECT * FROM USER";
    //用于分页查询数据库中的用户信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ?,?";

    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM USER WHERE USERPHONE=?";

    //录入用户
    public static final String SQL_INSERT = "INSERT INTO USER (USERNAME,USERPHONE,PASSWORD,REGISTERTIME,LOGINTIME) VALUES(?,?,?,NOW(),NOW())";
    //用户修改
    public static final String SQL_UPDATE = "UPDATE USER SET USERNAME=?,USERPHONE=?,PASSWORD=? WHERE ID=?";
    //用户的删除
    public static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";


    /**
     * 用于查询用户数量和新增数量
     *
     * @return{size:用户总数，day:新增数量}
     */
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            state = conn.prepareStatement(SQL_CONSOLE);

            result = state.executeQuery();
            if(result.next()){
                int size = result.getInt("size");
                int day = result.getInt("day");

                data.put("size",size);
                data.put("day",day);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * 用于查询所有用户
     *
     * @param limit      是否有分页标记，true为分页,false表示查询所有用户
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();

        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            if(limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);

                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            result = state.executeQuery();

            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String password = result.getString("password");
                Timestamp registerTime =result.getTimestamp("registertime");
                Timestamp logintime = result.getTimestamp("logintime");
                User u = new User(id,username,userPhone,password,registerTime,logintime);
                data.add(u);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 根据用户的手机号码，查询用户
     *
     * @param userPhone 手机号码
     * @return 查询的用户信息列表
     */
    @Override
    public User findByUserPhone(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            state.setString(1,userPhone);

            result = state.executeQuery();

            if(result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String password = result.getString("password");
                Timestamp registerTime =result.getTimestamp("registertime");
                Timestamp logintime = result.getTimestamp("logintime");
                User u = new User(id,username,userPhone,password,registerTime,logintime);
                return u;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用户的录入
     *
     * @param u 要录入的对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(User u) throws DuplicateCodeException, DuplicateCodeException {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement(SQL_INSERT);

            state.setString(1,u.getUsername());
            state.setString(2,u.getUserPhone());
            state.setString(3,u.getPassword());
            return state.executeUpdate()>0? true:false;

        } catch (SQLException e1) {
            /*throwables.printStackTrace();*/
            if(e1.getMessage().endsWith("for key 'userPhone'")){
                //是因电话重复,而出现了异常
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else{
                e1.printStackTrace();
            }
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户的修改
     *
     * @param id      要修改的用户id
     * @param newUser 新的用户对象(username,userPhone,password)
     * @return
     */
    @Override
    public boolean update(int id, User newUser) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newUser.getUsername());
            state.setString(2,newUser.getUserPhone());
            state.setString(3,newUser.getPassword());
            state.setInt(4,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id，删除单个用户信息
     *
     * @param id 要删除的用户id
     * @return 删除的结果，true表示成功
     */
    @Override
    public boolean delete(int id) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
